<?php
/**
 * 数据库操作类
 *
 * Created		: 2011-06-14
 * Modified		: 2011-07-06
 * @link		: http://www.itblog.org
 * @copyright	: (C) 2011 ITBlog Inc.
 * @version		: 1.0.0
 * @author		: Joseph Chen (jsph.chen@gmail.com)
 */
class Db extends pdo
{
	public static $debug = true;

	private static $connectList = array();
	private static $host = 'localhost';
	private static $name = '';
	private static $user = 'root';
	private static $pass = '';
	private static $charset = 'utf8';
	private static $persistent = false;
	private static $dns = '';
	private static $options = null;
	private static $tbl_pre = '';
	private static $array_save_handler = 'json';

	protected static $rows = 0;
	protected $priKey = 'id';

	protected static $instance = null;

	protected static $logSqlcodes = array(1054,1064);

	public static $errors = null;
	public static $queries = 0;
	public static $stmt = null;

	// 控制是否可以结束事务(commit或rollback)
	private static $canEndTransaction = true;
	private static $isStartTransaction = false;// 事务是否已开启



	/**
	 * 初始化并连接
	 * @param array $cfg
	 */
	public static function init($cfg=null, $sign='default')
	{
		if (is_array($cfg)) {
			isset($cfg['host']) && self::$host = $cfg['host'];
			isset($cfg['name']) && self::$name = $cfg['name'];
			isset($cfg['user']) && self::$user = $cfg['user'];
			isset($cfg['pass']) && self::$pass = $cfg['pass'];
			isset($cfg['charset']) && self::$charset = $cfg['charset'];
			if (isset($cfg['array_save_handle'])) {
				self::$array_save_handler = $cfg['array_save_handle'];
			}
			isset($cfg['persistent']) && self::$persistent = $cfg['persistent'];
			// 表前缀
			isset($cfg['tbl_pre']) && self::$tbl_pre = $cfg['tbl_pre'];
		}
		self::$dns = 'mysql:dbname='.self::$name.';host='.self::$host;
		self::$options = array(
			PDO::MYSQL_ATTR_INIT_COMMAND => 'set names \''.self::$charset.'\'',
			PDO::ATTR_PERSISTENT => self::$persistent
		);
		try {
			self::$instance = new PDO( self::$dns, self::$user, self::$pass, self::$options);
		} catch (Exception $e) {
			return false;
		}
		self::$connectList[$sign] = self::$instance;
		//self::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
		return self::$instance;
	}

	/**
	 * 切换使用的DB连接
	 * @param string $sign
	 */
	public static function switchDb($sign)
	{
		if (isset(self::$connectList[$sign])) {
			self::$instance = self::$connectList[$sign];
			return self::$instance;
		} else {
			return false;
		}
	}
	
	/**
	 * 获取当前数据类实例对象
	 */
	public static function getInstance() 
	{
		return self::$instance;
	}
	
	/**
	 * 设置/获取当前数据库数组数据保存的处理方式 
	 * @param string $handler
	 */
	public static function arraySaveHandler($handler='') 
	{
		if ($handler) {
			self::$array_save_handler = $handler;
		} else {
			return self::$array_save_handler;
		}
	}

	/**
	 * 设置事务是否可提交状态
	 * Enter description here ...
	 * @param unknown_type $state
	 */
	public static function setTransactionState($state)
	{
		self::$canEndTransaction = $state;
	}

	/**
	 * (non-PHPdoc)
	 * @see PDO::beginTransaction()
	 */
	public static function startTransaction()
	{
		if (self::$isStartTransaction) {
			return true;
		} else {
			self::$instance->beginTransaction();
			self::$isStartTransaction = true;
			// $canEndTransaction不可在这里设置true,必须让业务层,处理这个属性
			return true;
		}
	}

	/**
	 * (non-PHPdoc)
	 * @see PDO::commit()
	 */
	public static function commitTransaction()
	{
		if (self::$isStartTransaction) {
			if (self::$canEndTransaction) {
				self::$instance->commit();
				self::$isStartTransaction = false;
			}
			return true;
		} else {
			return false;
		}
	}

	/**
	 * (non-PHPdoc)
	 * @see PDO::commit()
	 */
	public static function rollBackTransaction()
	{
		if (self::$isStartTransaction) {
			if (self::$canEndTransaction) {
				self::$instance->rollBack();
				self::$isStartTransaction = false;
			}
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 执行SQL语句
	 * @param string $sql 要执行的sql语句
	 * @param array | boolean $param 当执行参数化查询时需要的参数
	 * 			当$param设为true时表示执行多条sql语句
	 * @see PDO::exec()
	 */
	public static function execute($sql, $param=null)
	{
		self::$queries++;
		if (empty($sql) && self::$debug) {
			debug_print_backtrace();
			exit;
		}
		if (is_null($param)) {
			self::$rows = self::$instance->exec($sql);
			if (self::$rows === false) {
				self::$errors = self::$instance->errorInfo();
				if (in_array(self::$errors[1], self::$logSqlcodes)) {
					self::$errors['sql'] = $sql;
					self::$errors['params'] = $param;
				}
				self::errors(self::$errors);
				return false;
			} else {
				return true;
			}
		} else {
			self::$stmt = self::$instance->prepare($sql);
			if (is_array($param)) {
				$param_list = array();
				foreach ($param as $k=>$v) {
					$param_list[$k] = $v;
				}
				$param = $param_list;
				$result = self::$stmt->execute($param);
			} else {
				$result = self::$stmt->execute();
			}
			if (false === $result) {
				self::$errors = self::$stmt->errorInfo();
				if (in_array(self::$errors[1], self::$logSqlcodes)) {
					self::$errors['sql'] = $sql;
					self::$errors['params'] = $param;
				}
				self::errors(self::$errors);
				self::$rows = 0;
			} else {
				self::$errors = null;
				self::$rows = self::$stmt->rowCount();
			}
			return $result;
		}
	}

	/**
	 * 返回上次执行操作的受影响记录数
	 */
	public static function affectRows()
	{
		return self::$rows;
	}

	/**
	 * 获取记录数
	 * @param string $table
	 * @param string $where
	 */
	public static function getCounts($table, $where='', $param=null)
	{
		$sql = 'select count(*) from `'.$table.' where '.$where;
		return (int)self::getOne($table, $where, 'count(*)', $param);
	}

	/**
	 * 根据指定表名，字段和查询条件获取其中的数据
	 * @param string $table
	 * @param string $field
	 * @param string $where
	 * @param string $param
	 * @param string $order
	 * @param int $offset
	 * @param int $limit
	 * @return array
	 */
	public static function select($table, $where='', $field='*', $param=null, $order='', $offset=0, $limit=0)
	{
		empty($field) && $field = '*';
		empty($where) || $where =" where $where";
		empty($order) || $order =" order by $order";
		$sql = 'select '.$field.' from `'.$table.'`'.$where;
		$sql .= $order;
		if ($limit) {
			$sql .= ' limit '.$offset.','.$limit.' ';
		}
		empty($param) && $param=1;
		empty($style) && $style=PDO::FETCH_ASSOC;
		$ret = self::execute($sql, $param);
		// 只有一个字段,则只取第一列数据
		if (self::$stmt->columnCount() == 1) {
			$style = PDO::FETCH_COLUMN;
		}
		$ret = self::$stmt->fetchAll($style);
		if ($ret === false) {
			self::$errors = self::$stmt->errorInfo();
			if (isset(self::$errors[1]) && in_array(self::$errors[1], self::$logSqlcodes)) {
				self::$errors['sql'] = $sql;
				self::$errors['params'] = $param;
			}
			self::errors(self::$errors);
		} else {
			self::$errors = null;
		}
		// 取完数据要关闭游标,避免表被锁定
		self::$stmt->closeCursor();
		self::$stmt = null;
		return $ret;
	}

	/**
	 * 读取表中的一条符合条件的记录
	 * @param string $table
	 * @param string $where
	 * @param string $field
	 * @param string $param
	 */
	public static function getOne($table, $where='', $field='*', $param=null, $order='')
	{
		empty($field) && $field = '*';
		empty($where) || $where =" where $where";
		if ($order) {
			$sql = 'select '.$field.' from `'.$table.'` '.$where.' order by '.$order.' limit 1';
		} else {
			$sql = 'select '.$field.' from `'.$table.'` '.$where.' limit 1';
		}
		// 设置$param参数以使exec()方法执行读取结果集的操作
		empty($param) && $param=1;
		empty($style) && $style=PDO::FETCH_ASSOC;
		$ret = self::execute($sql, $param);
		$ret = self::$stmt->fetch(PDO::FETCH_ASSOC);
		if ($ret === false) {
			self::$errors = self::$stmt->errorInfo();
			if (isset(self::$errors[1]) && in_array(self::$errors[1], self::$logSqlcodes)) {
				self::$errors['sql'] = $sql;
				self::$errors['params'] = $param;
			}
		} else {
			self::$errors = null;
		}
		if (self::$stmt->columnCount() == 1 && !empty($ret)){
			$ret = current($ret);
		}
		// 取完数据要关闭游标,避免表被锁定
		self::$stmt->closeCursor();
		self::$stmt = null;
		return $ret;
	}

	/**
	 * 直接从SQL语句获取数据
	 * @param $sql SQL语句
	 * @param $multi 是否获取多条
	 * @param $param SQL参数
	 * @param $fetch_style 获取数据的字段方式
	 */
	public static function fetchFromSql($sql, $multi=true, $param=null, $fetch_style=PDO::FETCH_ASSOC)
	{
		empty($param) && $param=1;
		empty($fetch_style) && $fetch_style=PDO::FETCH_ASSOC;
		$cnt = self::execute($sql, $param);
		if ($multi) {
			// 只有一个字段,则只取第一列数据
			if (self::$stmt->columnCount() == 1) {
				$fetch_style = PDO::FETCH_COLUMN;
			}
			$ret = self::$stmt->fetchAll($fetch_style);
				
			self::$stmt->closeCursor();
			self::$stmt = null;
			return $ret;
		} else {
			// 只有一个字段,则只取第一列数据
			if (self::$stmt->columnCount() == 1) {
				$fetch_style = PDO::FETCH_COLUMN;
			}
			$ret = self::$stmt->fetchAll($fetch_style);
			if ($ret === false) {
				self::$errors = self::$stmt->errorInfo();
				if (in_array(self::$errors[1], self::$logSqlcodes)) {
					self::$errors['sql'] = $sql;
					self::$errors['params'] = $param;
				}
				self::errors(self::$errors);
			} else {
				self::$errors = null;
			}
			// 取完数据要关闭游标,避免表被锁定
			self::$stmt->closeCursor();
			self::$stmt = null;
			return $ret;
		}

	}

	/**
	 * 获取表中指定的字段值的一条记录
	 * @param $table
	 * @param $value 条件查询的值
	 * @param $field 条件查询的字段(默认主键)
	 * @param $fetch_field 要获取的字段列表
	 */
	public static function read($table, $value, $field=null, $fetch_field='*')
	{
		if (is_null($field)) {
			if (!empty(self::$priKey)) {
				$field = self::$priKey;
			} else {
				$field = 'id';
			}
		}
		$where = '`'.$field.'`=:'.$field;
		$param = array($field=>$value);
		return self::getOne($table, $where, $fetch_field, $param);
	}

	/**
	 * 向数据插入一组数据
	 * @param string $table
	 * @param array $data 要更新的数据列表(数组形式
	 * @return int | false
	 */
	public static function insert($table, $data, $multi=null)
	{
		$param = array();
		$fieldList = array();
		$valueList = array();
		// 多维数组,一次插入多条数据
		if ($multi || is_array(current($data))) {
			$dataNums = count($data);
			$needFieldList = 1;
			$i = 1;
			foreach ($data as $fa) {
				$tmpValues = array();
				foreach ($fa as $field => $value)
				{
					$needFieldList && $fieldList[] = '`'.$field.'`';
					$param_key = ':'.$field.$i;
					$tmpValues[] = $param_key;
					if (is_array($value)) {
						if (self::$array_save_handler == 'json') {
							$value = json_encode($value);
						} else {
							$value = serialze($value);
						}
					}
					$param[$param_key] = $value;
				}
				$i++;
				$needFieldList = 0;
				$tmpValues = join(', ', $tmpValues);
				$valueList[] = '('.$tmpValues.')';
			}
			$fieldStr = join(', ', $fieldList);
			$valueStr = join(",\n", $valueList);
			$sql = "INSERT INTO `$table` ($fieldStr) VALUES $valueStr";
		} else {
			$dataNums = 1;
			foreach ($data as $field => $value) {
				$fieldList[] = '`'.$field.'`';
				$param_key = ':'.$field;
				$valueList[] = $param_key;
				if (is_array($value)) {
					if (self::$array_save_handler == 'json') {
						$value = json_encode($value);
					} else {
						$value = serialze($value);
					}
				}
				$param[$param_key] = $value;
			}
			$fieldStr = join(', ', $fieldList);
			$valueStr = join(', ', $valueList);
			$sql = "INSERT INTO `$table` ($fieldStr) VALUES ($valueStr)";
		}
		$ret = self::execute($sql, $param);
		return $ret;
	}

	/**
	 * 获取上次插入记录的ID
	 * @see PDO::lastInsertId()
	 */
	public static function insertId($name=null)
	{
		return self::$instance->lastInsertId($name);
	}

	/**
	 * 获取自增字段当前值
	 * @param string $table_name
	 */
	public static function getAutoIncrement($table_name)
	{
		$sql = 'SHOW TABLE STATUS LIKE \''.$table_name.'\'';

		$record = self::fetchFromSql($sql, false);
		return $record['Auto_increment'];
	}

	/**
	 * 更新一个表的数据
	 * @param string $table
	 * @param array $data 要更新的数据列表(数组形式)
	 * @param string $where 查询条件字符串
	 * @param string $param SQL查询参数
	 * @param string $retSql 只返回生成的SQL语句,而不是执行
	 * @return int | false
	 */
	public static function update($table, $data, $where=null, $param=null, $retSql=0)
	{
		$set_list = array();
		is_null($param) && $param=array();
		if (is_array($data)) {
			foreach ($data as $field => $value) {
				if (false !== strpos($value, '`')) {
					$set_list[] = '`'.$field.'`='.$value;
				} else {
					$param_key = ':'.$field;
					$set_list[] = '`'.$field.'`='.$param_key;
					$param[$param_key] = $value;
				}
			}
			$set = implode(',', $set_list);
		} else {
			$set = $data;
		}
		if ($where) {
			$where = ' where ' . $where;
		}
		$sql = 'update `' . $table . '` set ' . $set . $where;
		$ret = self::execute($sql, $param);
		return $ret;
	}

	/**
	 * 一个表的字段数据进行增加
	 * @param string $table
	 * @param int|float $value
	 * @param string $field
	 * @param string $where
	 * @return int | false
	 */
	public static function addVal($table, $value, $field, $where=null)
	{
		if ($where) {
			$where = ' where ' . $where;
		}
		$sql = "update `{$table}` set `{$field}`=`{$field}`+{$value} {$where}";
		
		$ret = self::execute($sql);
		return $ret;
	}

	/**
	 * 执行删除操作
	 * @param string $table
	 * @param string $where
	 * @return int | false
	 */
	public static function delete($table, $where, $param=null)
	{
		if ($where) {
			$sql = 'delete from `'.$table.'` where '.$where;
			$ret = self::execute($sql, $param);
			return $ret;
		} else {
			$this->errors(
				$GLOBALS['DBDeleteWhereCannotEmpty']
			);
			return false;
		}
	}

	/**
	 * 获取数据库中所有的表
	 * @return array()
	 */
	public static function showTables()
	{
		$sql = 'show tables';
		$arr = self::fetchFromSql($sql, true, null, PDO::FETCH_COLUMN);
		$result = array();
		foreach ($arr as $v) {
			$result[] = $v;
		}
		return $result;
	}

	/**
	 * 获取表字段详细说明
	 * @param string $tbl
	 */
	public static function desc($tbl)
	{
		$exists = self::isTableExist($tbl);

		if (!$exists) {
			self::errors(
			array(
			0 => '0001',
			1 => 'table '.$tbl.' not exists in '.self::$name
			),
			false
			);
			return false;
		}
		$sql = 'desc `'.$tbl.'`';
		return self::fetchFromSql($sql);
	}
	
	/**
	 * 获取表的主键字段名
	 * @param string $tbl
	 */
	public static function getPk($tbl) 
	{
		$fieldList = self::desc($tbl);
		foreach ($fieldList as $v) {
			if ($v['Key'] == 'PRI') {
				return $v['Field'];
			}
		}
		return false;
	}

	/**
	 * 指定表是否存在
	 * @param string $tbl
	 */
	public static function isTableExist($tbl)
	{
		$sql = 'SHOW TABLES LIKE \''.$tbl.'\'';
		$ret = self::fetchFromSql($sql, false);

		return $ret;
	}

	/**
	 * 设置/返回错误信息
	 */
	public static function errors($errors=false, $log=false)
	{
		if (!PRODUCT_SERVER && $errors !== false) {
			echo "DB Errors: ";
			if (is_string($errors)) {
				echo $errors;
			} else {
				var_export($errors);
			}
			echo "\n<pre>\n";
			debug_print_backtrace();
			echo "\n</pre>\n";
		}
		if ($errors === false) {
			return self::$errors;
		} else {
			if ($log) {
				if (is_array($errors)) {
					$log = var_export($errors, true);
				} else {
					$log = $errors;
				}
				App::log($log);
			}
			self::$errors = $errors;
			return self::$errors;
		}
	}
}



















